﻿namespace QLPhongKham.Access.VienPhi
{
    using ConfigDatabase.Class;
    using Npgsql;
    using QLPhongKham.Access;
    using QLPhongKham.BaseClass.Chung;
    using QLPhongKham.BaseClass.VienPhi;
    using System;
    using System.Data;

    public class VBaoCaoDoanhThu
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public VBaoCaoDoanhThu()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public VBaoCaoDoanhThu(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public DataTable f_loaddata_chitiet(string tungay, string denngay, bool bbenhnhan)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select b.", dtovpThanhToanVPCT.columnIDNOIDUNG, " as idvp,b.", dtovpThanhToanVPCT.columnTENNOIDUNG, " as tenvp,b.", dtovpThanhToanVPCT.columnMANOIDUNG, " as mavp,b.", dtovpThanhToanVPCT.columnDVT, " as dvt,case when b.", dtovpThanhToanVPCT.columnLOAINOIDUNG, "=", HangSo.LoaiNoiDungVienPhi.enLoaiNoiDungVP.Thuoc.GetHashCode(), " then 1 else 0 end  as thuoc,case when b.", dtovpThanhToanVPCT.columnLOAINOIDUNG, "=", HangSo.LoaiNoiDungVienPhi.enLoaiNoiDungVP.Thuoc.GetHashCode(), 
                " then 'Thuốc' else 'Dịch vụ' end  as tenloai,sum(b.", dtovpThanhToanVPCT.columnSOLUONGTHU, ") as soluong,b.", dtovpThanhToanVPCT.columnDONGIA, " as dongia,sum(b.", dtovpThanhToanVPCT.columnSOLUONGTHU, " *b.", dtovpThanhToanVPCT.columnDONGIA, ") as thanhtien,sum(b.", dtovpThanhToanVPCT.columnTHUCTHU, ") as thucthu from ", str, dtovpThanhToanVP.tableName, " a inner join ", str, dtovpThanhToanVPCT.tableName, 
                " b on a.", dtovpThanhToanVP.columnID, "=b.", dtovpThanhToanVPCT.columnIDTTVP, " where a.", dtovpThanhToanVP.columnHOANTRA, "=0", (tungay != "") ? (" and to_timestamp(to_char(a." + dtovpThanhToanVP.columnNGAYTHU + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')") : "", (denngay != "") ? (" and to_timestamp(to_char(a." + dtovpThanhToanVP.columnNGAYTHU + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')") : "", bbenhnhan ? (" and a." + dtovpThanhToanVP.columnIDQL + ">0") : "", " group by b.", dtovpThanhToanVPCT.columnIDNOIDUNG, ",b.", dtovpThanhToanVPCT.columnTENNOIDUNG, ",b.", dtovpThanhToanVPCT.columnMANOIDUNG, 
                ",b.", dtovpThanhToanVPCT.columnDVT, ",b.", dtovpThanhToanVPCT.columnLOAINOIDUNG, ",b.", dtovpThanhToanVPCT.columnDONGIA, " order by b.", dtovpThanhToanVPCT.columnTENNOIDUNG, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_thutien(long id, string tungay, string denngay, int loaithutien, bool hoantra)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtovpThanhToanVP.columnID + " as id,a." + dtovpThanhToanVP.columnSOPHIEU + " as sophieu,a." + dtovpThanhToanVP.columnHOADON + " as hoadon,to_char(a." + dtovpThanhToanVP.columnNGAYTHU + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtovpThanhToanVP.columnIDDANGNHAP + " as iddangnhap,b." + dtoDMDangNhap.columnTENNGUOIDUNG + " as tennguoidangnhap,a." + dtovpThanhToanVP.columnHOTEN + " as hoten,a." + dtovpThanhToanVP.columnNAMSINH + " as namsinh,a." + dtovpThanhToanVP.columnIDGIOITINH + " as idgioitinh,a." + dtovpThanhToanVP.columnDIACHI + " as diachi,a." + dtovpThanhToanVP.columnGHICHU + " as ghichu,a." + dtovpThanhToanVP.columnTONGTIEN + " as tongcong,a." + dtovpThanhToanVP.columnTHUCTHU + " as thucthu,a." + dtovpThanhToanVP.columnIDQL + " as idql from " + str + dtovpThanhToanVP.tableName + " a left join " + str + dtoDMDangNhap.tableName + " b on a." + dtovpThanhToanVP.columnIDDANGNHAP + "=b." + dtoDMDangNhap.columnID + " where 1=1 " + ((id == 0L) ? "" : string.Concat(new object[] { " and a.", dtovpThanhToanVP.columnID, "=", id })) + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtovpThanhToanVP.columnNGAYTHU + ",'dd/mm/yyyy'),'dd/mm/yyyy')>=to_timestamp('" + tungay + "','dd/mm/yyyy')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtovpThanhToanVP.columnNGAYTHU + ",'dd/mm/yyyy'),'dd/mm/yyyy')<=to_timestamp('" + denngay + "','dd/mm/yyyy')")) + (!hoantra ? (" and " + dtovpThanhToanVP.columnHOANTRA + "=0") : "");
            if (loaithutien == 1)
            {
                sql = sql + " and a." + dtovpThanhToanVP.columnIDQL + ">0";
            }
            else if (loaithutien == 2)
            {
                sql = sql + " and a." + dtovpThanhToanVP.columnIDQL + "=0";
            }
            sql = sql + " order by a." + dtovpThanhToanVP.columnNGAYTHU + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }
    }
}

